package quanlyhochieu.Thongketheothoigian;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import dbutil.ConnectDB;

import utils.DateUtils;
import utils.UtilsOnScopes;

public class BeanThongKeTheoThoiGian {
	private String error;
	private java.util.Date  starDate;
	private java.util.Date endDate;
	private String path ="/qlhochieu/theothoigian/emptyBieuMau.xhtml" ;
	private List<Table> list;
	
	
	
	
	
	/**************************************
	 * Method name		: viewImage
	 * Return type		: String
	 * Description		:Truy van lay len hinh anh
	 * Created date		: Aug 16, 2008
	 * Author			: Honglk
	 **************************************/
	public  String viewImage() throws IOException {
		ConnectDB.connectDB();
		this.setError("");
		if(this.checkValue(this.starDate, this.endDate)== false){
			return "";
		}
		this.delete();
		
		Date starDateSQL = DateUtils.utilDateToSqlDate(this.getStarDate());
		Date endDateSQL = DateUtils.utilDateToSqlDate(this.getEndDate());
		List<Table> list = new ArrayList<Table>();
		byte[] arrData = new byte[10024];
		String SQL = "SELECT DISTINCT A.ten,A.namsinh,A.gioitinh,A.coquan,A.anh,A.dangvien,A.chucvu,A.cmnd " +
				" FROM canhan A inner join dsnguoidi C  on A.cmnd = C.cmnd inner join doanra D on D.madoan =C.iddoanra "
				+" WHERE (D.ngaydi BETWEEN ' " + starDateSQL + "' AND '" + endDateSQL + "')" +
				   " AND C.trangthaidi=true" +
				   " AND D.flagdelete = 0";
		System.out.println(SQL);
		
		InfoPasport passportInfo = new InfoPasport();
		
		try {
			 ResultSet resultSet = ConnectDB.statement.executeQuery(SQL);
			 String realPath = UtilsOnScopes.getSession().getServletContext().getRealPath(
					 			"qlhochieu/theothoigian/image/");
			int canRead = 0;
			String cmnd ="";
			int stt = 0;
		
			while (resultSet.next()) {
				Table table = new Table();
				stt =stt+1;
				String ten = resultSet.getString("ten") ;
				String namsinhTemp =resultSet.getString("namsinh")==null?"":resultSet.getString("namsinh");
				String gioitinh = resultSet.getBoolean("gioitinh")==true?"Nam":"Nữ";
				String coquan = resultSet.getString("coquan");
				String dangvien = resultSet.getBoolean("dangvien")==true?"Có":"Không";
				String chucvu = resultSet.getString("chucvu");;
				cmnd = resultSet.getString("cmnd");
				
				File fileImage = new File(realPath+"/"+cmnd+".jpg");
				FileOutputStream fos = new FileOutputStream(fileImage);
				BufferedOutputStream bos = new BufferedOutputStream(fos);
				BufferedInputStream bis = null;
				bis = new BufferedInputStream(resultSet.getBinaryStream("anh"));
				System.out.println("duong dan :"+realPath);
				
				while ((canRead = bis.read(arrData)) != -1) {
					bos.write(arrData, 0, canRead);
				}
				bos.flush();
				bos.close();
				//Thong tin ca nhan
				table.setTen(ten);
				table.setCoquan(coquan);
				table.setDangvien(dangvien);
				table.setGioitinh(gioitinh);
				table.setPathImage("image/"+cmnd+".jpg");
				table.setNamsinh(namsinhTemp);
				System.out.println("duong dan image:" +table.getPathImage());
				// end
				table.setStt(String.valueOf(stt));
				//Thong tin ve ho chieu
				passportInfo = this.getInfoPassport(cmnd, starDateSQL, endDateSQL);
				table.setHochieuphothong(passportInfo.getBuilderhcPhoThong().toString());
				table.setHochieucongvu(passportInfo.getBuilderhcCongVu().toString());
				table.setHochieungoaigiao(passportInfo.getBuilderhcNgoaiGiao().toString());
				table.setNoicap(passportInfo.getBuilderhcNoiCap().toString());
				table.setNgaycap(passportInfo.getBuilderhcNgayCap().toString());
				table.setNgayhethan(passportInfo.getBuilderhcNgayHetHan().toString());
				table.setInfoOthers(passportInfo.getInfoOthers());
				//End
				
				list.add(table);
			}
			this.setList(list);
			if(this.getList() != null && this.getList().size()> 0){
				this.setPath("/qlhochieu/theothoigian/bieumau.xhtml");
			}else {
				this.setError("Không có dữ liệu !");
				return "";
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			System.out.println(" SQL is invalid");
		}
		return "okThongKeTheoThoiGian";
	}
	
	/**************************************
	 * Method name		: delete
	 * Return type		: void
	 * Description		:xoa file hinh cu
	 * Created date		: Jul 27, 2008
	 * Author			: Honglk
	 **************************************/
	public void delete() {
		 String realPath = UtilsOnScopes.getSession().getServletContext().getRealPath("qlhochieu/theothoigian/image/");
		try {
			File file = new File(realPath);
			if (file.isDirectory()) {
				File[] files = file.listFiles();
				for (File file2 : files) {
					if (file2.isFile()) {
						System.out.println(file2.delete());
						file2.delete();
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**************************************
	 * Method name		: getInfo
	 * Return type		: String
	 * Description		:Lay thong tin lien quan de nguoi
	 * Created date		: Aug 17, 2008
	 * Author			: Honglk
	 **************************************/
	public InfoOthers getInfo(String sohc,java.sql.Date starDateSQL,java.sql.Date endDateSQL){
//		
		StringBuilder builderhcNgayGiaoHC = new StringBuilder();
		StringBuilder builderhcNgayNhanHC = new StringBuilder();
		StringBuilder builderhcNuocDen = new StringBuilder();
		StringBuilder builderhcNgayDi = new StringBuilder();
		StringBuilder builderhcNgayVe = new StringBuilder();
		StringBuilder builderhcSoQD = new StringBuilder();
		StringBuilder builderhcTinhTrang = new StringBuilder();
		
		InfoOthers in = new InfoOthers();
		
		String SQL = "select * from  dsnguoidi C  inner join doanra D on D.madoan =C.iddoanra " +
				        " inner join quyetdinh F on D.idquyetdinh = F.id " +
				        " inner join (select madoan,sohc,max(G.ngaythaotac) as ngaytrathucte,min(G.ngaythaotac) as ngaynhanhc"
				        + 			  " from nhatkytranhanhc G group by G.madoan,G.sohc) K "+
				        " on (C.sohc = K.sohc and K.madoan = C.iddoanra)" +
				        " WHERE (D.ngaydi BETWEEN ' " + starDateSQL + "' AND '" + endDateSQL + "')" +
						   "AND C.sohc='" +sohc +"'" +
						   " AND C.trangthaidi=true" +
						   " AND D.flagdelete = 0";
						 
		System.out.println(SQL);
		ArrayList<HashMap<String, Object>> array = ConnectDB
		.getDataTypeList(SQL);
		try {
			if (array.size() > 0) {
				HashMap<String, Object> hash = new HashMap<String, Object>();
				for (int i = 0; i < array.size(); i++) {
					hash = (HashMap<String, Object>) array.get(i);
					String ngaytrathucte = DateUtils.formatDate(hash.get("ngaytrathucte")==null?"":hash.get("ngaytrathucte").toString());
					String ngaygiao = DateUtils.formatDate(hash.get("ngaynhanhc")==null?"":hash.get("ngaynhanhc").toString());
					if(ngaytrathucte.equalsIgnoreCase(ngaygiao)){
						builderhcNgayGiaoHC.append(ngaygiao+"<br/>");
						builderhcTinhTrang.append("Chưa nộp<br/>");
						builderhcNgayNhanHC.append(""+"<br/>");
					
					}else{
						builderhcNgayGiaoHC.append(""+"<br/>");
						builderhcTinhTrang.append("Chưa nộp<br/>");
						builderhcNgayNhanHC.append(ngaytrathucte+"<br/>");
						
						
					}
					String nuocden = hash.get("nuocden").toString();
					String ngaydi = hash.get("ngaydi").toString();
					String ngayve = hash.get("ngayve").toString();
					String soQD = hash.get("soqd").toString();
					
					builderhcNuocDen.append(nuocden+"<br/>");
					builderhcNgayDi.append(ngaydi+"<br/>");
					builderhcNgayVe.append(ngayve+"<br/>");
					builderhcSoQD.append(soQD+"<br/>");
				}
			in.setBuilderhcNgayDi(builderhcNgayDi);
			in.setBuilderhcNgayGiaoHC(builderhcNgayGiaoHC);
			in.setBuilderhcNgayNhanHC(builderhcNgayNhanHC);
			in.setBuilderhcNgayVe(builderhcNgayVe);
			in.setBuilderhcNuocDen(builderhcNuocDen);
			in.setBuilderhcSoQD(builderhcSoQD);
			in.setBuilderhcTinhTrang(builderhcTinhTrang);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return in;
	}
	
	/**************************************
	 * Method name		: getInfoPassport
	 * Return type		: String
	 * Description		:lay ra danh sach HC tuong ung voi nguoi
	 * Created date		: Aug 16, 2008
	 * Author			: Honglk
	 **************************************/
	public InfoPasport getInfoPassport(String cmnd,java.sql.Date starDateSQL,java.sql.Date endDateSQL) {
		String SQL = "SELECT * FROM hochieu hc " +
				"WHERE hc.cmnd ='" + cmnd + "'";
		System.out.println(SQL);
		StringBuilder builderhcNgoaiGiao = new StringBuilder();
		StringBuilder builderhcCongVu = new StringBuilder();
		StringBuilder builderhcPhoThong = new StringBuilder();
		StringBuilder builderhcNoiCap = new StringBuilder();
		StringBuilder builderhcNgayCap = new StringBuilder();
		StringBuilder builderhcNgayHetHan = new StringBuilder();
		

		ArrayList<HashMap<String, Object>> array = ConnectDB
				.getDataTypeList(SQL);
		InfoPasport passportInfo = new InfoPasport();
		List<InfoOthers> infoOthers = new ArrayList<InfoOthers>();
		try {
			if (array.size() > 0) {
				HashMap<String, Object> hash = new HashMap<String, Object>();
				for (int i = 0; i < array.size(); i++) {
					InfoOthers infoOther = new InfoOthers();
					hash = (HashMap<String, Object>) array.get(i);
					String sohc = hash.get("sohc").toString();
					String loaiHC = hash.get("loaihc").toString();
					infoOther = this.getInfo(sohc, starDateSQL, endDateSQL);
					if(loaiHC.equalsIgnoreCase("Phổ thông") || (loaiHC.toUpperCase().startsWith("P")== true)){
						builderhcPhoThong.append(sohc+"<br/>");
						builderhcCongVu.append(""+"<br/>");
						builderhcNgoaiGiao.append(""+"<br/>");
					}else if(loaiHC.equalsIgnoreCase("Công vụ") || (loaiHC.toUpperCase().startsWith("C")== true)){
						builderhcPhoThong.append(""+"<br/>");
						builderhcCongVu.append(sohc+"<br/>");
						builderhcNgoaiGiao.append(""+"<br/>");
					}
					else if(loaiHC.equalsIgnoreCase("Ngoại giao") || (loaiHC.toUpperCase().startsWith("N")== true)){
						builderhcPhoThong.append(""+"<br/>");
						builderhcCongVu.append(""+"<br/>");
						builderhcNgoaiGiao.append(sohc+"<br/>");
					}
					String noicap = hash.get("noicap").toString();
					builderhcNoiCap.append(noicap+"<br/>");
					
					String ngaycap = DateUtils.formatDate(hash.get("ngaycap").toString());
					builderhcNgayCap.append(ngaycap+"<br/>");
					
					String ngayhethan = DateUtils.formatDate(hash.get("ngayhethan").toString());
					builderhcNgayHetHan.append(ngayhethan+"<br/>");	
					
					infoOthers.add(infoOther);
					
				}
				passportInfo.setBuilderhcCongVu(builderhcCongVu);
				passportInfo.setBuilderhcNgayCap(builderhcNgayCap);
				passportInfo.setBuilderhcNgayHetHan(builderhcNgayHetHan);
				passportInfo.setBuilderhcNgoaiGiao(builderhcNgoaiGiao);
				passportInfo.setBuilderhcNoiCap(builderhcNoiCap);
				passportInfo.setBuilderhcPhoThong(builderhcPhoThong);
				passportInfo.setInfoOthers(infoOthers);
				System.out.println("so pt:"+passportInfo.getInfoOthers().size());
			}
		} catch (Exception e) {

		}
		return passportInfo;
	}
	
	/**************************************
	 * Method name		: reset
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 16, 2008
	 * Author			: Honglk
	 **************************************/
	public String reset() {
		this.delete();
		UtilsOnScopes.getSession().removeAttribute("beanThongKeTheoThoiGian");
		return "okThongKeTheoThoiGian";
	}
	
	
	/**************************************
	 * Method name		: checkValue
	 * Return type		: boolean
	 * Decription		: Kiem tra du lieu dau vao
	 * Created date		: Jun 18, 2008
	 * Author			: Honglk
	 **************************************/
	public boolean checkValue( java.util.Date star,java.util.Date  end){
		boolean result = true;
		if(star == null || star.equals("")){
			this.setError("Bạn chưa chọn ngày bắt đầu !");
			return result = false;
		}
		else if(end == null || end.equals("")){
			this.setError("Bạn chưa chọn ngày kết thúc !");
		  return result = false;
		}
		else if(star.compareTo(end) > 0){
			this.setError("Ngày kết thúc lớn hơn ngày bắt đầu !");
			return result = false;
			
		}
		return result;
	}
	public String getError() {
		return error;
	}
	public void setError(String error) {
		this.error = error;
	}
	public java.util.Date getStarDate() {
		return starDate;
	}
	public void setStarDate(java.util.Date starDate) {
		this.starDate = starDate;
	}
	public java.util.Date getEndDate() {
		return endDate;
	}
	public void setEndDate(java.util.Date endDate) {
		this.endDate = endDate;
	}
	public String getPath() {
		return path;
	}
	public void setPath(String path) {
		this.path = path;
	}
	public List<Table> getList() {
		return list;
	}
	public void setList(List<Table> list) {
		this.list = list;
	}
	
	public static void main(String[] args) {
		System.out.println("Hồng".substring(0));
		
	}
	
}
